package com.jiashihui.server1.utils;

import jxl.Cell;
import jxl.DateCell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import org.springframework.util.StringUtils;

import java.io.*;
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.TimeZone;

/**
 * @ClassName ExcelUtil
 * @Description TODO
 * @Author Administrator
 * @Date 2018-12-18 16:07
 * @Version 1.0
 **/
public class ExcelUtil {
    private static final String DEV0001 = "DEV0001";
    private static final String DEV0003 = "DEV0003";
    private static final String DEV0004 = "DEV0004";
    private static final String FLAG = "1";

    public static void main(String[] args) throws IOException {
        read("C:\\Users\\jiashihui\\Desktop\\222\\sj11.xls");
    }

    public static void read(String path){
        File f=new File(path);
        try {
            Workbook book=Workbook.getWorkbook(f);
            for(int j = 0; j < book.getSheets().length; j++){
                Sheet sheet=book.getSheet(j);
                Flaut flaut = new Flaut();
                List<Flaut> flautList = new ArrayList<>();
                for(int i=0;i<sheet.getRows();i++){
                    if( sheet.getCell(5, i)!= null && !FLAG.equals(sheet.getCell(5, i).getContents())){
                        continue;
                    }
                    String cell3 = sheet.getCell(3, i).getContents();
                    if(DEV0001.equals(cell3) && StringUtils.isEmpty(flaut.getLineCode())){
                        flaut.setLineCode(sheet.getCell(1, i).getContents());
                        flaut.setLineName(sheet.getCell(2, i).getContents());
                        flaut.setStartTime(format(sheet.getCell(4, i)));
                    } else if(!cell3.contains("DE")){
                        flaut.setLineCode(sheet.getCell(1, i).getContents());
                        flaut.setLineName(sheet.getCell(2, i).getContents());
                        flaut.setStartTime(format(sheet.getCell(3, i)));
                        flaut.setEndTime(format(sheet.getCell(4, i)));
                        flaut.setDifference(difference(flaut));
                        flautList.add(flaut);
                        flaut = new Flaut();
                    } else if((DEV0003.equals(cell3) || DEV0004.equals(cell3))
                            && !StringUtils.isEmpty(flaut.getStartTime())){
                        flaut.setEndTime(format(sheet.getCell(4, i)));
                        flaut.setDifference(difference(flaut));
                        flautList.add(flaut);
                        flaut = new Flaut();
                    }
                }
                writeExcel(flautList,j);
            }
        } catch (BiffException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    private static String difference(Flaut flaut){
        StringBuilder result = new StringBuilder();
        Date startTime = timeToDate(flaut.getStartTime());
        Date endTime = timeToDate(flaut.getEndTime());
        long diff = endTime.getTime() - startTime.getTime();
        long days = diff / (1000 * 60 * 60 * 24);
        long hours = (diff-days*(1000 * 60 * 60 * 24))/(1000* 60 * 60);
        long minutes = (diff-days*(1000 * 60 * 60 * 24)-hours*(1000* 60 * 60))/(1000* 60);
        result.append(days + "天" + hours + "小时" + minutes + "分钟");
        return result.toString();
    }

    private static Date timeToDate(String dateString) {
        Date date = new Date();
        DateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSS");
        try {
            date = sdf.parse(dateString);
        } catch (ParseException e) {
            e.printStackTrace();
        }
        return date;
    }

    private static String format(Cell cell){
        DateCell dc = (DateCell) cell;
        Date date = dc.getDate();
        TimeZone zone = TimeZone.getTimeZone("GMT");
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSS");
        sdf.setTimeZone(zone);
        return sdf.format(date);
    }

    public static void writeExcel(List<Flaut> flautList,int j) throws IOException {
        try{
            Workbook wbook = Workbook.
                    getWorkbook(new File("C:\\Users\\jiashihui\\Desktop\\222\\1.0各场地运行时间统计.xls"));
            WritableWorkbook wwbCopy = Workbook.
                    createWorkbook(new File("C:\\Users\\jiashihui\\Desktop\\222\\" + flautList.get(0).getLineName() + ".xls"), wbook);
            WritableSheet shSheet = wwbCopy.getSheet(0);
            for (int i = 0; i < flautList.size(); i++) {
                Flaut flaut = flautList.get(i);
                Label labTemp0 = new Label(0, i + 1 , flaut.getLineCode());
                Label labTemp1 = new Label(1, i + 1 , flaut.getLineName());
                Label labTemp2 = new Label(2, i + 1 , flaut.getStartTime());
                Label labTemp3 = new Label(3, i + 1 , flaut.getEndTime());
                Label labTemp4 = new Label(4, i + 1 , flaut.getDifference());
                shSheet.addCell(labTemp0);
                shSheet.addCell(labTemp1);
                shSheet.addCell(labTemp2);
                shSheet.addCell(labTemp3);
                shSheet.addCell(labTemp4);
            }
            wwbCopy.write();
            wwbCopy.close();
            wbook.close();
        } catch(Exception e) {
            e.printStackTrace();
        }
    }
}
